OpenEdge Data Management:<br />DataServer for ORACLE Retrieving results sets using proc-text-buffer

Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

Retrieving results sets using proc-text-buffer

The proc–text–buffer technique offers one approach to access results returned from a data source. The following information is the partial syntax for the DEFINE BUFFER statement that you use to create a buffer with the same characteristics of the proc–text–buffer:
Syntax
DEFINE BUFFER buffer-name FOR proc-text-buffer 
For a complete description, see the “DEFINE BUFFER statement” reference entry in OpenEdge Development: Progress 4GL Reference .

Technique to use proc-text-buffer

Example 3–6 illustrates returning database results into the proc–text-buffer and converting the results to the INTEGER data type.
DEFINE VAR handle1 AS INTEGER. 
DEFINE VAR imax as INT.
RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE
 ("SELECT max (cust_num) FROM customer").
FOR EACH proc-text-buffer:
 		imax = int (substring (proc-text, 1, 3)).
 		 DISPLAY imax.
END.
CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle1. 
Example 3–6: Returning database results into the proc-text-buffer and converting the results to the INTEGER data type

The DataServer passes the PL/SQL statement directly to the ORACLE data source. The Progress Compiler does not process it, so errors occur only at run time and not when you compile a procedure.

Assessing a result set obtained from the proc-text-buffer technique

The advantage of using the proc–text–buffer is that you do not have to worry about what kind of data the procedure returns. The buffer accepts any type of data, in any order, and converts it to the character equivalent. Another benefit of the proc–text–buffer is that it can be used to hold the results from all of the SQL statements included in a stored procedure. However, a buffer that you create can hold the results of only one result set record form one result set at a time.

However, a disadvantage in using the proc-text-buffer technique is that it is much more difficult to manipulate the data after you receive it as it requires parsing the data. To act on anything but CHARACTER data, you must extract the data from the buffer and convert it to its original data type before you can use it.